package com.sinotn.ks.node.export.process;

import java.io.File;
import java.io.FileOutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.regex.Pattern;
import java.util.zip.CRC32;

import org.apache.commons.io.FileUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HeaderFooter;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Footer;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.springframework.beans.factory.annotation.Autowired;

import com.sinotn.SinotnLogger;
import com.sinotn.SinotnRuntime;
import com.sinotn.error.MessageException;
import com.sinotn.gson.GsonFacade;
import com.sinotn.ks.definition.ExamEngine;
import com.sinotn.ks.definition.Examinee;
import com.sinotn.ks.node.meta.service.RoomService;
import com.sinotn.ks.node.meta.service.ServerAuthService;
import com.sinotn.ks.node.meta.vo.ServerAuthVO;
import com.sinotn.ks.node.runtime.dao.cond.AnswerPaperCond;
import com.sinotn.ks.node.runtime.service.PlanService;
import com.sinotn.ks.node.runtime.service.RuntimeService;
import com.sinotn.ks.node.runtime.service.UploadService;
import com.sinotn.ks.node.runtime.vo.AnswerPaperVO;
import com.sinotn.ks.node.runtime.vo.AnswerPaperVO.ExamState;
import com.sinotn.ks.node.runtime.vo.ExamineeVO;
import com.sinotn.ks.node.runtime.vo.RoomExamRoundVO;
import com.sinotn.task.DefaultProcessStatus;

public class XlsGradeExpProcess extends DefaultProcessStatus implements Runnable {
	@Autowired
	private ExamEngine examEngine;
	@Autowired
	private UploadService uploadService;
	@Autowired
	private RoomService roomService;
	@Autowired
	private PlanService planService;
	@Autowired
	private RuntimeService runtimeService;
	@Autowired
	private ServerAuthService serverAuthService;
	private String computerId;
	//private String ipAddr;
	//private String mac;
	//private String version;
	private final byte SEX_FEMALE=2;// 女
	private final byte SEX_MALE=1;// 男

	public XlsGradeExpProcess(String computerId,String ipAddr,String mac,String version) {
		this.id=UUID.randomUUID().toString();
		this.computerId=computerId;
		//this.ipAddr=ipAddr;
		//this.mac=mac;
		//this.version=version;
	}

	@Override
	public int getAutoClose() {
		return 3;
	}

	@Override
	public void run() {
		File workFolder=null;
		FileOutputStream fos=null;
		try{
			List<RoomExamRoundVO> rounds=this.runtimeService.findGradeRounds(computerId);
			if(rounds==null||rounds.isEmpty()){
				throw new MessageException("没有可导出成绩的考场场次数据");
			}
			ServerAuthVO serverAuthVo = this.serverAuthService.getServerAuth();
			Pattern p=Pattern.compile("<br[\\s/]*>");
			final String planName=p.matcher(serverAuthVo.getPlanName()).replaceAll("");

			HSSFWorkbook wb=new HSSFWorkbook();
			RoomExamRoundVO round;
			Sheet sheet;
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy年MM月dd日 HH:mm:ss");
			String beginTime;
			String endTime;
			String dayText;
			List<AnswerPaperVO> papers;
			AnswerPaperCond answerPaperCond;
			int size=rounds.size();
			Examinee examinee;
			ExamineeVO examineeVo;
			String examineeName;
			String certId;
			int seatNo;
			String workCode;
			String sex;
			String examGrade;
			int totalCount=0;
			Footer footer;
			DecimalFormat df=new DecimalFormat("0.#");
			CRC32 crc32;
			String validText;
			String roomName=null;
			for(int i=0;i<size;i++){
				round=rounds.get(i);
				if(roomName==null){
					roomName=round.getRoomName();
				}
				this.message="正在导出"+round.getRoomName();
				sheet=wb.createSheet();
				sheet.protectSheet("www.fstec.com");
				crc32 = new CRC32();
				sheet.setColumnWidth(0, 256*8);
				sheet.setColumnWidth(1, 256*10);
				sheet.setColumnWidth(2, 256*5);
				sheet.setColumnWidth(3, 256*20);
				sheet.setColumnWidth(4, 256*12);
				sheet.setColumnWidth(5, 256*8);
				sheet.setColumnWidth(6, 256*20);
				sheet.setColumnWidth(7, 256*8);

				CellStyle cellStyle = wb.createCellStyle(); // 单元格样式
				cellStyle.setWrapText(true);
				cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
				cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
				cellStyle.setBorderRight(CellStyle.BORDER_THIN);
				cellStyle.setBorderTop(CellStyle.BORDER_THIN);
				cellStyle.setAlignment(CellStyle.ALIGN_LEFT);// 水平居左
				cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直居中

				CellStyle centerStyle = wb.createCellStyle(); // 单元格样式
				centerStyle.setWrapText(true);
				centerStyle.setBorderBottom(CellStyle.BORDER_THIN);
				centerStyle.setBorderLeft(CellStyle.BORDER_THIN);
				centerStyle.setBorderRight(CellStyle.BORDER_THIN);
				centerStyle.setBorderTop(CellStyle.BORDER_THIN);
				centerStyle.setAlignment(CellStyle.ALIGN_CENTER);// 水平居中
				centerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直居中

				int rownum=0;
				Row row=sheet.createRow(rownum++);
				row.setHeight((short)600);
				row.createCell(0).setCellValue(planName+"\r\n 成绩签字确认表");

				short height=400;
				row=sheet.createRow(rownum++);
				row.setHeight(height);
				beginTime=sdf.format(round.getBeginTime());
				endTime=sdf.format(round.getEndTime());
				dayText=beginTime.substring(0, 11);
				if(endTime.startsWith(dayText)){
					endTime=endTime.substring(11);
				}
				if(beginTime.endsWith(":00") && endTime.endsWith(":00")){
					beginTime=beginTime.substring(0, 16);
					endTime=endTime.substring(0, endTime.length()-3);
				}
				row.createCell(0).setCellValue(beginTime+"-"+endTime+"场次");
				row.createCell(6).setCellValue(round.getRoomName());

				row=sheet.createRow(rownum++);
				row.setHeight(height);
				Cell cell=row.createCell(0);
				cell.setCellStyle(centerStyle);
				cell.setCellValue("座位号");
				cell=row.createCell(1);
				cell.setCellStyle(centerStyle);
				cell.setCellValue("姓名");
				cell=row.createCell(2);
				cell.setCellStyle(centerStyle);
				cell.setCellValue("性别");
				cell=row.createCell(3);
				cell.setCellStyle(centerStyle);
				cell.setCellValue("身份证号");
				cell=row.createCell(4);
				cell.setCellStyle(centerStyle);
				cell.setCellValue("岗位代码");
				cell=row.createCell(5);
				cell.setCellStyle(centerStyle);
				cell.setCellValue("成绩");
				cell=row.createCell(6);
				cell.setCellStyle(centerStyle);
				cell.setCellValue("签字");
				cell=row.createCell(7);
				cell.setCellStyle(centerStyle);
				cell.setCellValue("备注");

				//考生成绩
				answerPaperCond=new AnswerPaperCond();
				answerPaperCond.setRoundId(round.getId());
				papers=this.runtimeService.findAnswerPapers(answerPaperCond);
				for(AnswerPaperVO paper:papers){
					totalCount++;
					row=sheet.createRow(rownum++);
					row.setHeight(height);
					
					examinee=this.examEngine.getExaminee(paper.getExamineeId());
					if(examinee==null){
						examineeVo=this.planService.getExaminee(paper.getExamineeId());
						examineeName=examineeVo.getName();
						certId=examineeVo.getCertId();
						seatNo=examineeVo.getSeatNo();
						sex=this.getSexText(examineeVo.getSex());
						workCode=this.getWorkCode(examineeVo.getExtInfo());
					}else{
						examineeName=examinee.getName();
						certId=examinee.getCertId();
						seatNo=examinee.getSeatNo();
						sex=this.getSexText(examinee.getSex());
						workCode=this.getWorkCode(examinee.getExtInfo());
					}
					cell=row.createCell(0);
					cell.setCellStyle(centerStyle);
					cell.setCellValue(seatNo);//座位号
					// 姓名
					cell=row.createCell(1);
					cell.setCellStyle(centerStyle);
					cell.setCellValue(examineeName);
					//性别
					cell=row.createCell(2);
					cell.setCellStyle(centerStyle);
					cell.setCellValue(sex);
					// 身份证号
					cell=row.createCell(3);
					cell.setCellStyle(centerStyle);
					cell.setCellValue(certId);
					//岗位代码
					cell=row.createCell(4);
					cell.setCellStyle(centerStyle);
					cell.setCellValue(workCode);
					// 成绩
					examGrade=paper.getExamGrade();
					cell=row.createCell(5);
					cell.setCellStyle(centerStyle);
					if(StringUtils.isNotEmpty(examGrade)){
						examGrade=df.format(Double.valueOf(examGrade));
						cell.setCellValue(examGrade);
						validText=certId+"|"+examGrade;
						crc32.update(validText.getBytes("utf-8"));
					}
					// 签字
					cell=row.createCell(6);
					cell.setCellStyle(centerStyle);

					// 备注
					cell=row.createCell(7);
					cell.setCellStyle(centerStyle);
					if(paper.getFirstLoginTime()==null){
						cell.setCellValue("缺考");
					}else if(!ExamState.SUBMITED.is(paper.getExamState())){
						throw new MessageException(examineeName+"未交卷，不能导出数据文件");
					}
				}
				// 合并单元格
				CellRangeAddress cra =new CellRangeAddress(0, 0, 0, 7);
				sheet.addMergedRegion(cra);
				RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, cra, sheet, wb);
				RegionUtil.setBorderTop(CellStyle.BORDER_THIN, cra, sheet, wb);
				RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, cra, sheet, wb);
				RegionUtil.setBorderRight(CellStyle.BORDER_THIN, cra, sheet, wb);
				sheet.getRow(0).getCell(0).setCellStyle(centerStyle);

				cra =new CellRangeAddress(1, 1, 0, 5);
				sheet.addMergedRegion(cra);
				RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, cra, sheet, wb);
				RegionUtil.setBorderTop(CellStyle.BORDER_THIN, cra, sheet, wb);
				RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, cra, sheet, wb);
				RegionUtil.setBorderRight(CellStyle.BORDER_THIN, cra, sheet, wb);
				sheet.getRow(1).getCell(0).setCellStyle(centerStyle);

				cra =new CellRangeAddress(1, 1, 6, 7);
				sheet.addMergedRegion(cra);
				RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, cra, sheet, wb);
				RegionUtil.setBorderTop(CellStyle.BORDER_THIN, cra, sheet, wb);
				RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, cra, sheet, wb);
				RegionUtil.setBorderRight(CellStyle.BORDER_THIN, cra, sheet, wb);
				sheet.getRow(1).getCell(5).setCellStyle(centerStyle);
				
				this.persent=10+(int)((i+1)*80.0/size);
				// 页眉
				sheet.getHeader().setLeft("核验码："+Long.toHexString(crc32.getValue()).toUpperCase());
				// 页脚
				footer=sheet.getFooter();
				footer.setLeft("注：请仔细核对信息及成绩，确认无误后请在签字栏签字");
				footer.setRight(HeaderFooter.page()+"/"+HeaderFooter.numPages());
				// 分页表头行
				cra =new CellRangeAddress(2, 2, 0, 7);
				sheet.setRepeatingRows(cra);
			}

			workFolder=new File(SinotnRuntime.getTempFolder(), this.id);
			workFolder.mkdir();
			File file=new File(workFolder, roomName+"成绩签字确认表.xls");
			fos=new FileOutputStream(file);
			wb.write(fos);
			fos.close();
			fos=null;
			this.message="成功导出"+totalCount+"条成绩数据";
			this.success=true;
		}catch(MessageException e){
			this.message=e.getMessage();
		}catch(Throwable e){
			this.message="导出成绩数据发生系统错误";
			SinotnLogger.DEBUG.error(this.message,e);
		}finally{
			IOUtils.closeQuietly(fos);
			if(!this.success && workFolder!=null && workFolder.exists()){
				try{
					FileUtils.deleteDirectory(workFolder);
				}catch(Throwable e){}
			}
			this.persent=100;
		}
	}

	private String getWorkCode(String extInfo) {
		if(extInfo==null||extInfo.isEmpty()) return "";
		Map<?, ?> info=GsonFacade.fromJson(extInfo, HashMap.class);
		return (String)info.get("workCode");
	}

	private String getSexText(byte sex) {
		if(sex==SEX_FEMALE){
			return "女";
		}else if(sex==SEX_MALE){
			return "男";
		}
		return "未知";
	}
}
